package main.MysqlToOracle;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class MysqlToOracle {

    public static void main(String[] args) throws SQLException {
//        String mysqlCreateTable = "CREATE TABLE `crm_order_filling` (\n" +
//                "  `company_id` varchar(255) DEFAULT NULL COMMENT '公司id',\n" +
//                "  `company_name` varchar(255) DEFAULT NULL COMMENT '公司名称',\n" +
//                "  `org_id` varchar(255) DEFAULT NULL COMMENT '组织id',\n" +
//                "  `org_name` varchar(255) DEFAULT NULL COMMENT '组织名称',\n" +
//                "  `dept_id` varchar(255) DEFAULT NULL COMMENT '部门id',\n" +
//                "  `dept_name` varchar(255) DEFAULT NULL COMMENT '部门名称',\n" +
//                "  `dept_code` varchar(255) DEFAULT NULL COMMENT '部门编码',\n" +
//                "  `customer_country` varchar(255) DEFAULT NULL COMMENT '国家',\n" +
//                "  `amount` varchar(255) DEFAULT NULL COMMENT '订货计划金额',\n" +
//                "  `time_dimension` varchar(255) DEFAULT NULL COMMENT '时间类型',\n" +
//                "  `plan_date` varchar(255) DEFAULT NULL COMMENT '计划日期',\n" +
//                "  `partition_id` varchar(255) DEFAULT NULL COMMENT '分区id'\n" +
//                ") ";
        MysqlDdl ss = new MysqlDdl();
        Scanner sc = new Scanner(System.in);
        System.out.println("输入要转换的表");
        String mysqlTable = sc.nextLine();
        String mysqlCreateTable = ss.getTableDdl(mysqlTable);


        // 提取建表语句
        String oracleCreateTable = convertMySQLToOracle(mysqlCreateTable);
        oracleCreateTable = oracleCreateTable.replaceAll("`", " ");
        // 编译一个正则表达式模式，用于匹配和提取表注释
        Pattern tableCommentPattern = Pattern.compile("COMMENT='([^']*)'");
        // 创建一个Matcher对象，用于在MySQL建表语句中查找表注释
        Matcher tableCommentMatcher = tableCommentPattern.matcher(ss.getTableDdl(mysqlTable));
        // 如果找到匹配的表注释，则提取并赋值给tableComment
        String tableComment = "";
        if (tableCommentMatcher.find()) {
            tableComment = tableCommentMatcher.group(1);
        }
        tableComment="COMMENT ON TABLE " + mysqlTable + " IS '" + tableComment + "';";
        System.out.println(tableComment);



//        oracleCreateTable.replaceAll("`"," ");

        try (BufferedWriter writer = new BufferedWriter(new FileWriter("D:\\code\\json-parsing-data\\src\\test\\java\\main\\MysqlToOracle\\output.txt"))) {
            writer.write(oracleCreateTable);
            writer.write("\n");
            writer.write(tableComment);
//            writer.write(MysqlDdl.buff.toString());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static String convertMySQLToOracle(String mysqlCreateTable) {
        if (mysqlCreateTable == null || mysqlCreateTable.trim().isEmpty()) {
            throw new IllegalArgumentException("MySQL CREATE TABLE statement cannot be null or empty.");
        }

        // 提取表名
        String tableName = getTableName(mysqlCreateTable);

        // 移除MySQL的AUTO_INCREMENT，因为它在Oracle中没有直接等价物
        String oracleCreateTable = mysqlCreateTable
                .replaceAll("(?i)AUTO_INCREMENT", "")
                .replaceAll("(?i)utf8mb3_general_ci", "")
                .replaceAll("(?i)utf8mb3", "")
                .replaceAll("(?i)utf8mb4_0900_ai_ci", "")
                .replaceAll("(?i)utf8mb4", "")
                .replaceAll("(?i)COLLATE", "")
                .replaceAll("(?i)CHARACTER", "")
                .replaceAll("(?i)SET", "")
                .replaceAll("(?i)DEFAULT NULL", "")
                .replaceAll("(?i)\\s+DEFAULT NULL COMMENT\\s+'[^']+'", "");

        // 编译一个正则表达式来匹配主键定义，这是为了在Oracle中创建相应的SEQUENCE和TRIGGER
        Pattern autoIncrementPattern = Pattern.compile("(\\w+)\\s+INT\\s+PRIMARY\\s+KEY", Pattern.CASE_INSENSITIVE);
        Matcher matcher = autoIncrementPattern.matcher(oracleCreateTable);
        // 如果找到匹配的主键定义，则进行替换并添加SEQUENCE和TRIGGER的创建语句
        if (matcher.find()) {
            String columnName = matcher.group(1);
            // 替换主键定义，移除INT类型，因为Oracle不需要指定类型来创建SEQUENCE
            oracleCreateTable = matcher.replaceAll(columnName + " NUMBER PRIMARY KEY");
            // 添加SEQUENCE创建语句，用于生成主键值
            oracleCreateTable += "\nCREATE SEQUENCE SEQ_" + columnName.toUpperCase() + " START WITH 1 INCREMENT BY 1;\n";
            // 添加TRIGGER创建语句，用于在插入新记录前从SEQUENCE获取下一个值
            oracleCreateTable += "CREATE OR REPLACE TRIGGER TRG_" + columnName.toUpperCase() + "\n" +
                    "BEFORE INSERT ON " + tableName.toUpperCase() + "\n" +
                    "FOR EACH ROW\n" +
                    "BEGIN\n" +
                    "  SELECT SEQ_" + columnName.toUpperCase() + ".NEXTVAL INTO :NEW." + columnName.toUpperCase() + " FROM dual;\n" +
                    "END;\n";
        }

        // 提取列名和注释
        List<String> comments = new ArrayList<>();
        Pattern columnPattern = Pattern.compile("`([^`]+)`\\s+[^\\s]+\\s+(?:DEFAULT\\s+[^\\s]+\\s+)?COMMENT\\s+'([^']+)'", Pattern.CASE_INSENSITIVE);
        Matcher columnMatcher = columnPattern.matcher(oracleCreateTable);
        while (columnMatcher.find()) {
            String columnName = columnMatcher.group(1);
            String comment = columnMatcher.group(2);

            comments.add("COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + comment + "';");

        }

        // //COMMENT ON TABLE ads_sd_sales_order_details_full_1d IS '销售订单明细表';
        // 输出表注释
//        comments.add("COMMENT ON TABLE " + tableName + " IS '" + tableName + "';");

        // 将MySQL的数据类型转换为Oracle的数据类型
        oracleCreateTable = oracleCreateTable
                .replaceAll("(?i)DOUBLE\\(\\d+,\\d+\\)", "NUMBER")
                .replaceAll("(?i)DOUBLE", "NUMBER")
                .replaceAll("(?i)VARCHAR\\((\\d+)\\)", "VARCHAR2($1)")
                .replaceAll("(?i)DATETIME", "TIMESTAMP")
                .replaceAll("(?i)TINYINT\\(\\d+\\)", "NUMBER(3)")
                .replaceAll("(?i)SMALLINT\\(\\d+\\)", "NUMBER(5)")
                .replaceAll("(?i)MEDIUMINT\\(\\d+\\)", "NUMBER(7)")
                .replaceAll("(?i)INT\\(\\d+\\)", "NUMBER(10)")
                .replaceAll("(?i)INT", "NUMBER(10)")
                .replaceAll("(?i)BIGINT\\(\\d+\\)", "NUMBER(19)")
                .replaceAll("(?i)FLOAT\\(\\d+,\\d+\\)", "BINARY_FLOAT")
                .replaceAll("(?i)TEXT", "CLOB")
                .replaceAll("(?i)FLOAT", "BINARY_FLOAT");

        // 移除MySQL的COMMENT关键字及其内容
        oracleCreateTable = oracleCreateTable
                .replaceAll("(?i)\\s+DEFAULT NULL COMMENT\\s+'[^']+'", "")
                .replaceAll("(?i)\\s+COMMENT\\s+'[^']+'", "");

        // 构建最终的Oracle建表语句和注释语句
        StringBuilder finalOracleCreateTable = new StringBuilder(oracleCreateTable);
        for (String comment : comments) {
            finalOracleCreateTable.append("\n").append(comment);
        }

        return finalOracleCreateTable.toString();
    }

    private static String getTableName(String createTableStatement) {
        // 使用更宽松的正则表达式来匹配表名
        Pattern tableNamePattern = Pattern.compile("(?i)CREATE\\s+TABLE\\s+([\\w\"`]+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = tableNamePattern.matcher(createTableStatement);
        if (matcher.find()) {
            // 返回匹配到的表名，并去除可能的引号
            String tableName = matcher.group(1).replaceAll("^\"|\"$|^`|`$", "");
            return tableName;
        }
        throw new IllegalArgumentException("Could not find table name in CREATE TABLE statement.");
    }
}
